dbtプロジェクト構築に関する ベストプラクティス #3「中間データ層(Intermediate Layer)に関する検討事項」 #dbt
2024.02.27アライアンス事業部 エンジニアグループ モダンデータスタック(MDS)チームのしんやです。
dbtはクラウド型データウェアハウス（DWH）におけるデータ変換に特化したツールです。非常に使い勝手が良く便利なツールである一方、様々な機能が提供されているのでいざ使ってみよう！となると『何をどうやって作り上げていけば良いんだろう？』『この場合のルールや制限はどういうものがあるの？どういう取り決めをもって扱えば良いんだろう？』という風に思うこともあるかと思います。(実際私自身そう感じました)
そんなユーザーの疑問や悩みを解決する、いわゆるdbtユーザー向けのガードレール的な存在となりうるコンテンツがdbt社から展開されています。それが『dbtベストプラクティスガイド(Best practice guides)』です。構造、スタイル、セットアップなど、dbt Labsの現在の視点を通した「ベストプラクティス」がまとめられています。
そこで当エントリでは、幾つか展開されている「dbtベストプラクティスガイド」で紹介されているコンテンツの中から『dbtプロジェクト構築』に関するもの、データウェアハウスにおける『中間データ層(Intermediate Layer)』をどうdbtで構築していくかについて読み解いていきたいと思います。
目次
中間データ層におけるファイルとフォルダの検討事項
「ステージングデータ層(Staging Layer)」で、データの"原子"といえる生データ状態のデータを定義したことで、それらをより複雑で連携された、いわば"分子"の形に加工編集する準備が整いました。当エントリで言及する「中間データ層(Intermediate Layer)」では、データに命を吹き込むために使用する、特定の目的を持つさまざまな形式への加工を担う作業を行います。
当エントリでの内容を説明、解説するために、jaffle_shopプロジェクトのmodels/intermediate配下の構造を使います。
jaffle_shop
├── README.md
├── analyses
├── seeds
│ └── employees.csv
├── dbt_project.yml
├── macros
│ └── cents_to_dollars.sql
├── models
│ ├── intermediate
│ │ └── finance
│ │ ├── _int_finance__models.yml
│ │ └── int_payments_pivoted_to_orders.sql
│ ├── marts
│ │ ├── finance
│ │ │ ├── _finance__models.yml
│ │ │ ├── orders.sql
│ │ │ └── payments.sql
│ │ └── marketing
│ │ ├── _marketing__models.yml
│ │ └── customers.sql
│ ├── staging
│ │ ├── jaffle_shop
│ │ │ ├── _jaffle_shop__docs.md
│ │ │ ├── _jaffle_shop__models.yml
│ │ │ ├── _jaffle_shop__sources.yml
│ │ │ ├── base
│ │ │ │ ├── base_jaffle_shop__customers.sql
│ │ │ │ └── base_jaffle_shop__deleted_customers.sql
│ │ │ ├── stg_jaffle_shop__customers.sql
│ │ │ └── stg_jaffle_shop__orders.sql
│ │ └── stripe
│ │ ├── _stripe__models.yml
│ │ ├── _stripe__sources.yml
│ │ └── stg_stripe__payments.sql
│ └── utilities
│ └── all_dates.sql
├── packages.yml
├── snapshots
└── tests
└── assert_positive_value_for_total_amount.sql
フォルダ
中間データ層(Intermediate Layer)におけるフォルダ構成のベストプラクティスは以下の通り。
| ✅ すべき(dos) | ❌ べからず(dont’s) |
|---|---|
|
ビジネスグループに基づくサブディレクトリ構成： ステージングレイヤーと同様に、このモデルのレイヤーを独自の中間サブフォルダー内に格納。 ステージング層とは異なり、ここではビジネスに準拠する方向に移行し、ソース システムではなく、ビジネス上の関心領域ごとにモデルをサブディレクトリに分割。 |
---- |
ファイル名
中間データ層(Intermediate Layer)におけるファイル関連のベストプラクティスは以下の通り。
| ✅ すべき(dos) | ❌ べからず(dont’s) |
|---|---|
|
int_[entity]s_[verb]s.sql： ・中間層の内部ではさまざまな変換が発生する可能性があるため、それらに名前を付ける方法を厳密に指示することが難しくなります。 ・最良の指針は、中間層の動詞について考えることです。例えば以下のようなものが挙げられます。 ・ pivoted・ aggregated_to_user・ joined・ fanned_out_by_quantity・ funnel_created
・このサンプルプロジェクトでは、中間モデルを使用して支払いを注文単位にピボットするため、モデルに ・実体と動詞を二重アンダースコアで区切ることを好む人もいますが、これは好みの問題。経験則的にこの層ではエンティティと動詞の間に本質的なつながりが存在することが多く、そのためそれを維持することが困難になる。 |
---- |
上述のサンプルプロジェクトはある種の説明を目的としたものなので非常に単純な構造となっています。なのでこれくらいの"シンプルさ"の状態で済むのであれば正直ステージング後のレイヤーでこのレベルの分割はおそらく不要でしょう。
ですが、実環境、本番運用を想定した構成であればより複雑さを増すことになるでしょうし、ここで述べられているルールに従うことで効果・恩恵を受けることが出来るはずです。ここで実現したい目標は「唯一の真実の情報源」を作り上げることです。我々は財務とマーケティングを別々の注文モデルで運用することを望んでいません。そしてこれらの定義を1 つにまとめる手段としてdbtプロジェクトを使用したいと考えています。
この事を踏まえると、分割や最適化に関しては進め方を急ぎ過ぎない、早め過ぎないようにするというのもポイントです。マートモデルが10個未満で、それらの開発と使用に問題がない場合は、完全にサブディレクトリを使用しなくても構いません (ステージング層を除き、プロジェクトに新しいソース システムを追加するときに必ずサブディレクトリを実装する必要があります)。 プロジェクトが成長し、本当にそれらを必要とするようになったタイミングで作業を行う...という感じで良いと思います。
中間データ層におけるモデルの検討事項
以下に記載したコードは、小規模なサンプルプロジェクトにおける「唯一の中間モデル」です。 これは、上記の原則に従った優れたユースケースを表しており、ステージングモデルをグループ化し、異なる粒度(テーブル内のレコードが一意になる列の組み合わせ)にピボットするという明確な1つの目的を果たしています。
ここではモデルをDRY化するために、Jinjaを少し利用しています (DRYであることの努力は、コードベース全体の変換に加えて、単一モデル内で記述するコードにも適用されます) が、あまり慣れていない場合でも怖がる必要はありません。
CTE(Common Table Expression/共通テーブル式)の名前を見ると、pivot_and_aggregate_payments_to_order_granという定義が使われており、このブロック内で何が起こっているのかが非常に明確になります。 ファイルやフォルダーの場合と同じように、モデル内の CTE 内で発生する変換に説明的なラベルを付けることで、SQL を知らない関係者でも、コードではないにしても、このセクションの目的を理解できるようになります。
ステージング層から離れて、より複雑な変換を書き始める際はこの考えを念頭に置いてください。 モデルがDAGに接続し、マクロスケールで変換のストーリーを伝えるのと同じ方法で、CTE はモデルファイル内でこれをより小規模なスケールで行うことができます。
-- int_payments_pivoted_to_orders.sql
{%- set payment_methods = ['bank_transfer','credit_card','coupon','gift_card'] -%}
with
payments as (
select * from {{ ref('stg_stripe__payments') }}
),
pivot_and_aggregate_payments_to_order_grain as (
select
order_id,
{% for payment_method in payment_methods -%}
sum(
case
when payment_method = '{{ payment_method }}' and
status = 'success'
then amount
else 0
end
) as {{ payment_method }}_amount,
{%- endfor %}
sum(case when status = 'success' then amount end) as total_amount
from payments
group by 1
)
select * from pivot_and_aggregate_payments_to_order_grain
| ✅ すべき(dos) | ❌ べからず(dont’s) |
|---|---|
|
マテリアライゼーション(具体化)は「ephemerally(一時的な形)」で： 上記を考慮すると、一般的なオプションの1つは、中間モデルをデフォルトで「一時的な状態で実体化」(Materialized ephemerally)することです。 通常、簡単にするために、ここから始めるのが最適です。 最小限の構成で不要なモデルをウェアハウスから排除します。 ※ただし、エフェメラルは出力を表示できる形で単独で存在するのではなく、エフェメラルを参照するモデルに補間されるため、そのシンプルさによりトラブルシューティングが少し難しくなることに注意してください。 ・参考：Ephemeral - Materializations | dbt Developer Hub ・参考：[dbt] 生成するデータモデルの種類を変える「Materializations」を試す | DevelopersIO |
エンドユーザーへの公開： 中間モデルは通常、メインの実稼働スキーマで公開すべきものではありません。 ダッシュボードやアプリケーションなどの最終ターゲットへの出力を目的としていないため、データ ガバナンスと検出可能性をより簡単に制御できるように、モデルからは分離しておくことをお勧めします。 |
|
特別な権限を持つカスタムスキーマのビューとして実体化を検討： より堅牢なオプションは、メインの実稼働スキーマの外にある特定のカスタムスキーマ内のビューとして中間モデルを実体化することです。 これにより、実装が容易で占有スペースもごくわずかでありながら、開発に関する洞察がさらに深まり、モデルの数と複雑さが増大してもトラブルシューティングが容易になります。 ・参考：Custom schemas | dbt Developer Hub |
---- |
|
ウェアハウスの整理整頓を心掛ける： dbtにエンコードしている組織のナレッジグラフには「DAG」「コードベースのファイルとフォルダー構造」「ウェアハウスへの出力」という3つのインターフェイスがあります。従って、そのアウトプットを意図的に考慮することが非常に重要となります。ダッシュボード、ML、アプリ、およびデータの対象となるその他のユースケースに加えて、UX の一部としてウェアハウス内に作成しているスキーマ、テーブル、ビューについて考えてください。 これを達成するには、出力に名前が付けられ、適切にグループ化されていること、および広範な使用を目的としていないモデルが具体化されないか、特定の権限を持つ特別な領域に組み込まれていることを確認することが重要です。 |
---- |
中間モデルの目的は、マートモデルの複雑さを解消する役割を果たし、データ変換に必要なだけの形式を取ることにも繋がります。中間モデルの最も一般的な使用例には次のようなものがあります。
| ✅ すべき(dos) | ❌ べからず(dont’s) |
|---|---|
|
構造の簡素化： 適切な数(通常は4～6個)のエンティティまたは概念(ステージングモデル、またはおそらく他の中間モデル)をまとめて、同様の目的の別の中間モデルと結合してマートを生成します。 マートに10個の結合を持たせるのではなく、それぞれが複雑な部分を収容する2つの中間モデルを結合できるため、可読性、柔軟性、テスト表面積、およびコンポーネントに関する洞察が向上します。 |
---- |
|
粒度の再定義： 中間モデルは、適切な複合粒度にモデルをファンアウトまたは折りたたむためによく使用されます。 order_itemsのマートを構築している場合、数量列(quantity)に基づいて注文(orders)をファンアウトし、品目ごとに新しい1行を作成する必要があります。マートの明瞭さを維持し、他のコンポーネントと混合する前に粒子が正しいことをより簡単に確認するには、特定の中間モデルで実行するのが理想的です。
|
---- |
|
複雑な操作を分離： 特に複雑なロジックや理解しにくいロジック部分を独自の中間モデルに移動すると便利です。 これにより、改良やトラブルシューティングが容易になるだけでなく、この概念をより明確に読みやすい方法で参照できる後続のモデルが簡素化されます。 例えば、上記の数量( quantity)ファンアウトの例では、この複雑なロジック部分を分離することで、その変換を迅速にデバッグして徹底的にテストできるようになり、下流モデルが直感的に理解しやすい方法でorder_itemsを参照できるようになり、メリットが得られます。
|
---- |
|
DAGを狭くし、テーブルを広く： ・Marts レイヤーに到達してさまざまな出力の構築を開始するまでは、DAG が右を向いた矢印のように見えることが理想。 ・ソース準拠からビジネス準拠に移行するにつれて、多数の狭い孤立した概念から、より少数のより広範な結合された概念に移行するイメージ。 ・dbtのモデリング作業を介して、ユーザーはコンポーネントをより幅広く、より豊富なコンセプトに統合しており、それによってDAGにこの形状が作成される。 ・マート層に到達すると、さまざまな質問に答え、特定のニーズに応えるために、迅速かつ簡単に任意の構成に組み込むことができる堅牢なコンポーネントのセットが得られるようになる。 ・個々のモデルレベルでこのパターンに従っていることを確認するための経験則の1つは、モデルへの複数の入力は許可するが、複数の出力は許可しないことが大事。 ・ステージング後のモデルにいくつかの矢印が入っていることは素晴らしいことであり、期待されていますが、いくつかの矢印が出ている場合は危険信号。このルールを破る必要がある状況は絶対にありますが、それは認識し、注意し、可能な限り避ける必要がある。 |
---- |
まとめ
という訳で、dbtプロジェクト構築に関するベストプラクティス第3弾としてデータウェアハウスにおける「中間データ層」に関する取り組み方の紹介でした。(前回第2弾からだいぶ間隔が空いてしまった...反省)
中間データ層はその名前が示すようにあくまでも「生データ層(ステージング層)」と「マート層」の間に位置するレイヤーです。文中「必ずしも無ければならない、ということは無い」という言及もあったように割と流動的な位置付けでもあるので実際の案件で適用させる場合も試行錯誤が多く入る場所になるのかな、とは思いました。少ないながらも有効な"プラクティス"が提示されていますので、ここはそれらの方針を上手く活用して運用していきたいですね。
![[小ネタ]dbt-databricksでINCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_WEEK_BASED_PATTERNエラーが発生したときの対処方法](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
![[Tips] dbt のドキュメントをStreamlitで表示する方法](https://images.ctfassets.net/ct0aopd36mqt/wp-refcat-img-a0f70de29a00d596aa50512303131e72/2fcbfee571e6db80cd904c3c64f7cecc/dbt-1200x630-1.jpg)
